#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys, datetime, json, logging, os
import pandas as pd
# from apscheduler.schedulers.blocking import BlockingScheduler
import numpy as np

from xpy3lib.utils.XDataFrameUtils import XDataFrameUtils
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave

from xpy3lib.utils import db_utils as util
from xpy3lib.XLogger import XLogger
from sicost.config import app_config

"""

-------------------------------->> NOTE <<------------------------------------------------ 
Usage:
c512cpc.py
------------------------------------------------------------------------------------------


"""


def main():



    profile = 'dev'
    config = app_config[profile]

    try:
        db_conn_sts = util.getConnectionSTS(config.DB_HOST_STS,
                                             config.DB_PORT_STS,
                                             config.DB_DBNAME_STS,
                                             config.DB_USER_STS,
                                             config.DB_PASSWORD_STS)
        XLogger.getInstance().logger.info('connect db_conn_mpp success')
        db_conn_mpp = util.getConnectionDb2(config.DB_HOST_MPP2_DB2,
                                            config.DB_PORT_MPP2_DB2,
                                            config.DB_DBNAME_MPP2_DB2,
                                            config.DB_USER_MPP2_DB2,
                                            config.DB_PASSWORD_MPP2_DB2)
        XLogger.getInstance().logger.info('connect db_conn_mpp success')
    except Exception as e:
        db_conn_sts = None
        db_conn_mpp = None

        XLogger.getInstance().logger.critical(str(e))
    if db_conn_sts is None:
        return
    if db_conn_mpp is None:
        return
    start = datetime.datetime.now()

    p_day_1 = (start - datetime.timedelta(days=1)).strftime("%Y%m%d")
    p_day_2 = (start - datetime.timedelta(days=2)).strftime("%Y%m%d")
    start_time = p_day_2 + '200000'
    end_time = p_day_1 + '200000'
    # start_time = '20220101000000'
    # end_time = '20220111000000'
    sql = " DELETE FROM " \
          " BGTAMSZZ00.T_DWD_FACT_ZZMM_HR_SEC_COOL_STAT" \
          " WHERE 1=1 AND UNIT_CODE='H033'" \
          " AND PRODUCE_END_TIME >= '%s'" \
          " AND PRODUCE_END_TIME < '%s'" % (start_time, end_time)

    db_conn_mpp.execute(sql)
    sql = " SELECT  " \
          " ENTRY_MAT_NO as SLAB_NO,EXIT_MAT_NO as STRIP_NO,UNIT_CODE,MAT_ACTUAL_WT,PRODUCE_END_TIME  " \
          " from BGTAMSZZQM.T_DWD_FACT_ZZQM_MATERIAL_TREE " \
          " where UNIT_CODE='H033' " \
          " and PRODUCE_END_TIME>='%s' " \
          " and PRODUCE_END_TIME<'%s' " % (start_time, end_time)
    # self.logger.info(sql)
    print(sql)
    df_00 = XRetryableQuery(p_db_conn=db_conn_mpp, p_sql=sql, p_max_times=5).redo()
    success = df_00.empty is False
    if success is False:
        return
    df_00.columns = df_00.columns.str.upper()

    df_00['STRIP_NO'] = df_00['STRIP_NO'].astype(str)
    l0 = df_00['STRIP_NO'].values.tolist()
    r0 = "'%s'" % ("','".join(l0))

    sql = " select stripno as STRIP_NO,coolingpatternno  " \
          " from bgtasoh033.H033_CTC_DATAFROMSCC where stripno in (%s) " % (r0)
    # self.logger.info(sql)
    print(sql)

    df_01 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_01.empty is False
    if success is False:
        return
    df_01.columns = df_01.columns.str.upper()
    v = ['STRIP_NO']
    df_02 = pd.merge(df_00, df_01, on=v, how='left')
    df_02N = df_02[df_02['COOLINGPATTERNNO'] == 'N']
    success = df_02N.empty is False
    if success is False:
        return

    df_02N['STRIP_NO'] = df_02N['STRIP_NO'].astype(str)
    l02 = df_02N['STRIP_NO'].values.tolist()
    r02 = "'%s'" % ("','".join(l02))

    df_02N['SLAB_NO'] = df_02N['SLAB_NO'].astype(str)
    l03 = df_02N['SLAB_NO'].values.tolist()
    r03 = "'%s'" % ("','".join(l03))








    sql = " SELECT  " \
          " SLAB_NO,ADJUST_TEMP_CT  " \
          " from BGTAMSZZ00.T_DWD_FACT_ZZPM_HR_DESIGN_INFO " \
          " where  " \
          " UNIT_CODE='H033' " \
          " and SLAB_NO in (%s) "   %(r03)
    # self.logger.info(sql)
    print(sql)
    df_0 = XRetryableQuery(p_db_conn=db_conn_mpp, p_sql=sql, p_max_times=5).redo()
    success = df_0.empty is False
    if success is False:
        return
    df_0.columns = df_0.columns.str.upper()
    w = ['SLAB_NO']
    df_0.drop_duplicates(subset=w, keep='first', inplace=True)
    v = ['SLAB_NO']
    df_02N0 = pd.merge(df_02N, df_0, on=v, how='left')
    df_base = df_02N0[['SLAB_NO', 'STRIP_NO', 'UNIT_CODE', 'MAT_ACTUAL_WT', 'PRODUCE_END_TIME', 'ADJUST_TEMP_CT']]
    df_02N0['ADJUST_TEMP_CT'].fillna(value=-100, inplace=True)
    df_02N0 = df_02N0[(df_02N0['ADJUST_TEMP_CT'] != -100)]



    sql = " select coilid as STRIP_NO,TEMP1,TEMP2  " \
          " from bgtasoh033.H033_L1_QDS_DCE where coilid in (%s) " % (r02)
    # self.logger.info(sql)
    print(sql)

    df_2 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_2.empty is False
    if success is False:
        return
    df_2.columns = df_2.columns.str.upper()
    df_2['TEMP1'].fillna(value='', inplace=True)

    def __cal_TEMP1_2(x):

        if x.TEMP1 == '':
            rst = x.TEMP2
        else:
            rst = x.TEMP1
        return rst

    df_2['TEMP1_2'] = df_2.apply(lambda x: __cal_TEMP1_2(x), axis=1)
    v = ['STRIP_NO']
    df_h033 = pd.merge(df_2, df_02N0, on=v, how='left')

    df_h033['NUM'] = 1
    def __cal_H033_JG(x):

        if x.TEMP1_2 >= x.ADJUST_TEMP_CT - 20 and x.TEMP1_2 <= x.ADJUST_TEMP_CT + 20:
            rst = 1
        else:
            rst = 0
        return rst

    df_h033['H033_JG'] = df_h033.apply(lambda x: __cal_H033_JG(x), axis=1)
    df_h033.drop(['TEMP1'], axis=1, inplace=True)
    df_h033.drop(['TEMP2'], axis=1, inplace=True)
    df_h033.drop(['TEMP1_2'], axis=1, inplace=True)
    df_h033.drop(['UNIT_CODE'], axis=1, inplace=True)
    df_h033.drop(['MAT_ACTUAL_WT'], axis=1, inplace=True)
    df_h033.drop(['PRODUCE_END_TIME'], axis=1, inplace=True)
    df_h033.drop(['COOLINGPATTERNNO'], axis=1, inplace=True)
    df_h033.drop(['ADJUST_TEMP_CT'], axis=1, inplace=True)

    w = ['STRIP_NO','SLAB_NO']

    a = df_h033.groupby(w)['H033_JG'].agg([np.sum]).round(2)
    a.rename(columns={'sum': 'SUM_H033_JG'}, inplace=True)
    b = df_h033.groupby(w)['NUM'].agg([np.sum]).round(2)
    b.rename(columns={'sum': 'SUM_NUM2'}, inplace=True)

    ab = pd.merge(a, b, on=w, how='left')
    d = df_h033[['STRIP_NO','SLAB_NO']]
    d.drop_duplicates(subset=w, keep='first', inplace=True)

    abd = pd.merge(d, ab, on=w, how='left')


    sql = " select strip_no ,co_temp_mid_cls_3+co_temp_mid_cls_4+co_temp_mid_cls_5+co_temp_mid_cls_6  as H033_JG  " \
          " from bgtasoh033.H033_SCC_FMSTRIP   where strip_no in (%s) " % (r02)
    # self.logger.info(sql)
    print(sql)

    df_8 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_8.empty is False
    if success is False:
        return
    df_8.columns = df_8.columns.str.upper()

    def __cal_LEVEL_H033(x):

        rst = 0
        if x.H033_JG / 100 > 0.9 :
            rst = 1
        elif x.H033_JG / 100 > 0.8 and x.H033_JG / 100 <=  0.9 :
            rst = 2
        elif x.H033_JG / 100 > 0.7 and x.H033_JG / 100 <= 0.8 :
            rst = 3
        elif x.H033_JG / 100 <= 0.7:
            rst = 4
        return rst

    df_8['LEVEL_H033'] = df_8.apply(lambda x: __cal_LEVEL_H033(x), axis=1)

    sql = " select coilid as STRIP_NO,l_dtgh_ctch_015 as MMP_TEMP1_2  " \
          " from bgtasoh033.H033_CTC_DC where coilid in (%s) " % (r02)
    # self.logger.info(sql)
    print(sql)

    df_3 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_3.empty is False
    if success is False:
        return
    df_3.columns = df_3.columns.str.upper()

    sql = " select stripno as STRIP_NO,middletemptarget as ADJUST_TEMP_CT  " \
          " from bgtasoh033.H033_CTC_DATAFROMSCC  where stripno in (%s) " % (r02)
    # self.logger.info(sql)
    print(sql)
    # , mid_coiler_temp_target_calc
    df_4 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_4.empty is False
    if success is False:
        return
    df_4.columns = df_4.columns.str.upper()





    v = ['STRIP_NO']
    df_h033_mmp = pd.merge(df_3, df_4, on=v, how='left')

    # v = ['SLAB_NO']
    # df_h033_mmp = pd.merge(df_34, df_0, on=v, how='left')
    df_h033_mmp['NUM'] = 1

    def __cal_H033_JG_MMP(x):

        if x.MMP_TEMP1_2 >= x.ADJUST_TEMP_CT - 20 and x.MMP_TEMP1_2 <= x.ADJUST_TEMP_CT + 20:
            rst = 1
        else:
            rst = 0
        return rst

    df_h033_mmp['H033_JG_MMP'] = df_h033_mmp.apply(lambda x: __cal_H033_JG_MMP(x), axis=1)

    df_h033_mmp.drop(['MMP_TEMP1_2'], axis=1, inplace=True)
    # df_h033_mmp.drop(['UNIT_CODE'], axis=1, inplace=True)
    df_h033_mmp.drop(['ADJUST_TEMP_CT'], axis=1, inplace=True)

    w = ['STRIP_NO']

    a2 = df_h033_mmp.groupby(w)['H033_JG_MMP'].agg([np.sum]).round(2)
    a2.rename(columns={'sum': 'SUM_H033_JG_MMP'}, inplace=True)
    b2 = df_h033_mmp.groupby(w)['NUM'].agg([np.sum]).round(2)
    b2.rename(columns={'sum': 'SUM_NUM'}, inplace=True)

    ab2 = pd.merge(a2, b2, on=w, how='left')
    d2 = df_h033_mmp[['STRIP_NO']]
    d2.drop_duplicates(subset=w, keep='first', inplace=True)

    abd2 = pd.merge(d2, ab2, on=w, how='left')

    def __cal_LEVEL_H033_MMP(x):

        rst = 0
        if x.SUM_H033_JG_MMP / x.SUM_NUM > 0.9:
            rst = 1
        elif x.SUM_H033_JG_MMP / x.SUM_NUM > 0.8 and x.SUM_H033_JG_MMP / x.SUM_NUM <= 0.9:
            rst = 2
        elif x.SUM_H033_JG_MMP / x.SUM_NUM > 0.7 and x.SUM_H033_JG_MMP / x.SUM_NUM <= 0.8:
            rst = 3
        elif x.SUM_H033_JG_MMP / x.SUM_NUM <= 0.7:
            rst = 4
        return rst

    abd2['LEVEL_H033_MMP'] = abd2.apply(lambda x: __cal_LEVEL_H033_MMP(x), axis=1)

    v = ['STRIP_NO']
    df_h033_final = pd.merge(df_8, abd2, on=v, how='outer')


    def __cal_H033_JG_FINAL(x):

        if x.LEVEL_H033_MMP >= x.LEVEL_H033:
            rst = x.LEVEL_H033_MMP
        else:
            rst = x.LEVEL_H033
        return rst

    df_h033_final['GRADE'] = df_h033_final.apply(lambda x: __cal_H033_JG_FINAL(x), axis=1)
    # df_h033_final.drop(['SLAB_NO'], axis=1, inplace=True)

    w = ['STRIP_NO']
    df_h033_finalend = pd.merge(df_base, df_h033_final, on=w, how='left')
    df_h033_finalend.drop(['H033_JG'], axis=1, inplace=True)
    df_h033_finalend.drop(['SUM_NUM'], axis=1, inplace=True)
    df_h033_finalend.drop(['SUM_H033_JG_MMP'], axis=1, inplace=True)
    # df_h033_finalend.drop(['SUM_NUM2'], axis=1, inplace=True)
    df_h033_finalend['LEVEL_H033_MMP'].fillna(value=0, inplace=True)
    df_h033_finalend['LEVEL_H033'].fillna(value=0, inplace=True)
    df_h033_finalend.rename(columns={'LEVEL_H033': 'JUDGE_RESULT'}, inplace=True)
    df_h033_finalend.rename(columns={'LEVEL_H033_MMP': 'JUDGE_RESULT_2'}, inplace=True)
    # df_h033_finalend.drop(['LEVEL_H033'], axis=1, inplace=True)
    # df_h033_finalend.drop(['LEVEL_H033_MMP'], axis=1, inplace=True)
    # df_h033_finalend.drop(['COOLINGPATTERNNO'], axis=1, inplace=True)
    # df_h033_finalend['GRADE'] = df_h033_finalend['GRADE'].astype(str)
    df_h033_finalend.rename(columns={'ADJUST_TEMP_CT': 'COILER_TEMP_TARGET'}, inplace=True)
    v = ['STRIP_NO']
    df_h033_finalend = pd.merge(df_h033_finalend, df_4, on=v, how='left')
    df_h033_finalend.rename(columns={'ADJUST_TEMP_CT': 'STRIP_MID_TEMP_TARGET'}, inplace=True)

    now = datetime.datetime.now()
    now_1 = now.strftime('%Y%m%d%H%M%S')
    # print(now_1)
    df_h033_finalend['REC_CREATOR'] = 'bgtamszz00'
    df_h033_finalend['REC_CREATE_TIME'] = now_1
    df_h033_finalend = df_h033_finalend[(df_h033_finalend['GRADE'] == 1) | (df_h033_finalend['GRADE'] == 2) | (df_h033_finalend['GRADE'] == 3) | (df_h033_finalend['GRADE'] == 4)]

    sql = " select stripno as STRIP_NO,coolspeed1 as STRIP_FIRST_SECTION_COOLING_RATE_TARGET ,coolspeed2 as STRIP_SEC_SECTION_COOLING_RATE_TARGET  " \
          " from bgtasoh033.H033_CTC_HMI_STRATEGY where stripno in (%s) " % (r02)
    # self.logger.info(sql)
    print(sql)
    df_7 = XRetryableQuery(p_db_conn=db_conn_sts, p_sql=sql, p_max_times=5).redo()
    success = df_7.empty is False
    if success is False:
        return
    df_7.columns = df_7.columns.str.upper()
    v = ['STRIP_NO']
    df_h033_finalend = pd.merge(df_h033_finalend, df_7, on=v, how='left')

    df_2['NUM'] = 1
    df_2.drop(['TEMP1'], axis=1, inplace=True)
    df_2.drop(['TEMP2'], axis=1, inplace=True)
    w = ['STRIP_NO']
    a3 = df_2.groupby(w)['TEMP1_2'].agg([np.sum]).round(2)
    a3.rename(columns={'sum': 'SUM_TEMP1_2'}, inplace=True)
    b3 = df_2.groupby(w)['NUM'].agg([np.sum]).round(2)
    b3.rename(columns={'sum': 'SUM_NUM3'}, inplace=True)
    ab3 = pd.merge(a3, b3, on=w, how='left')
    ab3['COILER_TEMP_MEAN'] = ab3['SUM_TEMP1_2'] / ab3['SUM_NUM3']
    ab3.drop(['SUM_TEMP1_2'], axis=1, inplace=True)
    ab3.drop(['SUM_NUM3'], axis=1, inplace=True)
    v = ['STRIP_NO']
    df_h033_finalend = pd.merge(df_h033_finalend, ab3, on=v, how='left')

    df_3['NUM'] = 1
    # df_3.drop(['MMP_TEMP_1'], axis=1, inplace=True)
    # df_3.drop(['MMP_TEMP_2'], axis=1, inplace=True)
    w = ['STRIP_NO']
    a4 = df_3.groupby(w)['MMP_TEMP1_2'].agg([np.sum]).round(2)
    a4.rename(columns={'sum': 'SUM_MMP_TEMP1_2'}, inplace=True)
    b4 = df_3.groupby(w)['NUM'].agg([np.sum]).round(2)
    b4.rename(columns={'sum': 'SUM_NUM4'}, inplace=True)
    ab4 = pd.merge(a4, b4, on=w, how='left')
    ab4['COIL_LAMINAR_MID_TEMP_TOTLEN_MEAN'] = ab4['SUM_MMP_TEMP1_2'] / ab4['SUM_NUM4']
    ab4.drop(['SUM_MMP_TEMP1_2'], axis=1, inplace=True)
    ab4.drop(['SUM_NUM4'], axis=1, inplace=True)
    v = ['STRIP_NO']
    df_h033_finalend = pd.merge(df_h033_finalend, ab4, on=v, how='left')





















    w = ['SLAB_NO', 'STRIP_NO']
    df_h033_finalend.drop_duplicates(subset=w, keep='first', inplace=True)



    #
    XRetryableSave(p_db_conn=db_conn_mpp, p_table_name='T_DWD_FACT_ZZMM_HR_SEC_COOL_STAT', p_schema='BGTAMSZZ00',
                   p_dataframe=df_h033_finalend,
                   p_max_times=5).redo()






    print('success')

    try:
        util.closeConnection(db_conn_sts)
        util.closeConnection(db_conn_mpp)
    except Exception as e:
        XLogger.getInstance().logger.error(str(e))
    print('每天01：30分执行该定时任务')

    pass

if __name__ == '__main__':
    start = datetime.datetime.now()

    status = main()
    elapsed = float((datetime.datetime.now() - start).seconds)
    print("Time Used 4 All ----->>>> %f seconds" % (elapsed))
